using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using GuavaData;
using GuavaLib;

namespace GuavaPerformanceAnalysis
{
    public partial class GuavaPerfMaster : Form
    {
        Channel channel1;
        Channel channel2;
        Channel resultsChannel;
        Random r;
        
        public GuavaPerfMaster()
        {
            InitializeComponent();

            // Initialize the channels
            DBConnection.Initialize("localhost", "GuavaPerf");
            channel1 = new Channel();
            channel2 = new Channel();
            resultsChannel = new Channel();
            r = new Random();

            RenameTable rt = new RenameTable();
            rt.add("TestTable", "StandardTable");
            channel2.Add(rt);

            List<string> lto = new List<string>();
            lto.Add("TestTable");
            Unpivot upo = new Unpivot(lto);
            channel1.Add(upo);

            MakeSchema(50);
        }

        private void MakeSchema(int numCols)
        {
            // Create the schema
            Schema s = new Schema();
            Scheme table = new Scheme("TestTable", null);
            s.tables.Add(table);
            table.newColumn("id", new GuavaData.Domain.UniqueIdentifier(), true, false);
            for (int i = 0; i < numCols; i++)
            {
                table.newColumn("data" + i.ToString(), new GuavaData.Domain.TinyInteger(), false, false);
            }

            // Create the schema
            Schema s2 = new Schema();
            Scheme table2 = new Scheme("TestTable", null);
            s2.tables.Add(table2);
            table2.newColumn("id", new GuavaData.Domain.UniqueIdentifier(), true, false);
            for (int i = 0; i < numCols; i++)
            {
                table2.newColumn("data" + i.ToString(), new GuavaData.Domain.TinyInteger(), false, false);
            }

            Schema r = new Schema();
            Scheme results = new Scheme(("Results"), null);
            r.tables.Add(results);
            results.newColumn("id", new GuavaData.Domain.UniqueIdentifier(), true, false);
            results.newColumn("test", new GuavaData.Domain.String(20), false, false);
            results.newColumn("isGeneric", new GuavaData.Domain.Boolean(), false, false);
            results.newColumn("numColumns", new GuavaData.Domain.Integer(), false, false);
            results.newColumn("numRows", new GuavaData.Domain.Integer(), false, false);
            results.newColumn("percentNull", new GuavaData.Domain.Integer(), false, false);
            results.newColumn("computationTime", new GuavaData.Domain.BigInteger(), false, false);
            results.newColumn("executionTime", new GuavaData.Domain.BigInteger(), false, false);
            results.newColumn("processingTime", new GuavaData.Domain.BigInteger(), false, false);


            // Create the schema for both channels
            //channel1.MakeSchema(s);
            //channel2.MakeSchema(s2);
            //resultsChannel.MakeSchema(r);
        }

        private void btnInitialize_Click(object sender, EventArgs e)
        {
            GenerateRows(Convert.ToInt32(this.txtNumColumns.Text), Convert.ToInt32(this.txtNumRows.Text), Convert.ToInt32(this.txtPercentNull.Text));
            
            // Input new data
            this.txtStatusBar.Text = "Done!";
        }

        private void btnQuery_Click(object sender, EventArgs e)
        {
            // Create query object
            Command c = new Command();
            string alias = c.nextTableAlias("TestTable");
            TableRef tr = new TableRef("TestTable", alias);
            tr.AddColumn("id", "TestTable_id", new GuavaData.Domain.UniqueIdentifier());
            tr.AddColumn("data2", "TestTable_data2", new GuavaData.Domain.TinyInteger());
            tr.AddColumn("data16", "TestTable_data16", new GuavaData.Domain.TinyInteger());
            Filter f = new Filter(tr);
            f.AddCondition("data7", Condition.NewCondition(ConditionType.LT, (byte)16));
            c.root = f;

            // Get data results
            string s;
            System.Data.DataSet ds = channel1.PoseQueryCommand(c, out s);
            dgvQueryResults.DataSource = ds.Tables[0];

            this.txtStatusBar.Text = "Done!";
        }

        private void TestOuterJoin()
        {
            Channel nullChannel = new Channel();
            channel1.InitializeDiagnostics();
            nullChannel.InitializeDiagnostics();

            // Create query object
            Command c = new Command();
            string alias = c.nextTableAlias("TestTable");
            TableRef tr = new TableRef("TestTable", alias);
            tr.AddColumn("data8", "TestTable_data8", new GuavaData.Domain.TinyInteger());
            tr.AddColumn("data17", "TestTable_data17", new GuavaData.Domain.TinyInteger());
            tr.AddColumn("data42", "TestTable_data42", new GuavaData.Domain.TinyInteger());
            tr.AddColumn("data12", "TestTable_data12", new GuavaData.Domain.TinyInteger());
            Filter f = new Filter(tr);
            f.AddCondition("data9", Condition.NewCondition(ConditionType.LE, 17));
            f.AddCondition("data11", Condition.NewCondition(ConditionType.LT, 52));
            f.AddCondition("data16", Condition.NewCondition(ConditionType.LE, 41));
            f.AddCondition("data32", Condition.NewCondition(ConditionType.GE, 43));
            c.root = f;
            string s;
            channel1.PoseQueryCommand(c, out s);

            this.txtStatusBar.Text = channel1.ExTime.ToString();
            //this.txtStatusBar.Text = channel1.ExTime.ToString() + ", " + nullChannel.ExTime.ToString();
            this.txtStatusBar.Refresh();
        }
        
        private void GenerateRows(int numCols, int numRows, int percentNulls)
        {
            // Clear existing data
            NonQuery nDel = new NonQuery(null);
            NQNode nqDel = new NQNode("TestTable", DMLType.DROPTABLE);
            nDel.Nodes.Add(nqDel);
            channel1.ApplyDML(nDel.Clone());
            channel2.ApplyDML(nDel);

            MakeSchema(numCols);

            try
            {
                for (int DML = 0; DML < numRows; DML++)
                {
                    // Add some data
                    NonQuery nq = new NonQuery(null);
                    nq.Add("TestTable", Guid.NewGuid(), DMLType.INSERT);
                    for (int i = 0; i < numCols; i++)
                    {
                        if (r.Next(0, 100) >= percentNulls) nq.AddValue("data" + i.ToString(), r.Next(64));
                    }
                    channel1.ApplyDML(nq.Clone());
                    channel2.ApplyDML(nq);
                    if (DML % 10 == 0) this.txtStatusBar.Text = DML.ToString();
                    this.txtStatusBar.Refresh();
                }
            }
            catch (Exception ex)
            {
                this.txtStatusBar.Text = ex.Message;
                return;
            }
        }

        private void btnStandardQueries_Click(object sender, EventArgs e)
        {
            //TestDataQuery();
            TestMain();
        }

        private void TestMain()
        {
            int numTrials = 50;
            //int[] lp = new int[] { 90, 95, 97, 98, 99 };
            int[] lp = new int[] { 60, 70, 80 };

            for (int i = 3; i <= 3; i++)
            {
                foreach (int percentNull in lp)
                {
                    List<string> lCols = new List<string>();
                    for (int l = 0; l < 50; l++)
                    {
                        lCols.Add("data" + l.ToString());
                    }

                    int numRows = 5000 * i;
                    //int percentNull = lp[j];

                    // Build dataset
                    this.GenerateRows(50, numRows, percentNull);
                    this.txtStatusBar.Text = "Rows generated";
                    this.txtStatusBar.Refresh();

                    for (int k = 0; k < 1; k++)
                    {
                        this.txtStatusBar.Text = String.Concat(i.ToString(), ", ", percentNull.ToString(), ", ", k.ToString());
                        this.txtStatusBar.Refresh();

                        // Run query instance test
                        for (int l = 0; l < numTrials; l++)
                        {
                            this.QueryInstance(lCols, numRows, percentNull);

                            //this.txtStatusBar.Text = String.Concat(i.ToString(), ", ", j.ToString(), ", ", k.ToString(), ": Q", l.ToString());
                            //this.txtStatusBar.Refresh();
                        }

                        // Run CRUD instance test
                        for (int l = 0; l < numTrials; l++)
                        {
                            this.CRUDInstance(lCols, numRows, percentNull);

                            //this.txtStatusBar.Text = String.Concat(i.ToString(), ", ", j.ToString(), ", ", k.ToString(), ": C", l.ToString());
                            //this.txtStatusBar.Refresh();
                        }

                        // Run drop column test
                        for (int l = 0; l < 4; l++)
                        {
                            this.DropColumnInstance(lCols, numRows, percentNull);

                            //this.txtStatusBar.Text = String.Concat(i.ToString(), ", ", j.ToString(), ", ", k.ToString(), ": D", l.ToString());
                            //this.txtStatusBar.Refresh();
                        }
                    }
                }
            }
        }

        private void DropColumnInstance(List<string> lCols, int numRows, int percentNull)
        {
            channel1.InitializeDiagnostics();
            channel2.InitializeDiagnostics();
            resultsChannel.InitializeDiagnostics();

            int colToDrop = r.Next(lCols.Count);

            // Create drop column statement
            NonQuery nq = new NonQuery(null);
            NQNode nqn = new NQNode("TestTable", DMLType.DROPCOLUMNS);
            nq.Nodes.Add(nqn);
            nqn.Add(lCols[colToDrop]);

            channel1.ApplyDML(nq.Clone());
            channel2.ApplyDML(nq);


            // Build a result row for output
            long ct1 = channel1.CompTime;
            long et1 = channel1.ExTime;
            long pt1 = channel1.PostTime;
            long ct2 = channel2.CompTime;
            long et2 = channel2.ExTime;
            long pt2 = channel2.PostTime;

            nq = new NonQuery(null);
            nq.Add("Results", Guid.NewGuid(), DMLType.INSERT);
            nq.AddValue("test", "DropCol");
            nq.AddValue("isGeneric", true);
            nq.AddValue("numColumns", lCols.Count);
            nq.AddValue("numRows", numRows);
            nq.AddValue("percentNull", percentNull);
            nq.AddValue("computationTime", ct1);
            nq.AddValue("executionTime", et1);
            nq.AddValue("processingTime", pt1);
            nq.Add("Results", Guid.NewGuid(), DMLType.INSERT);
            nq.AddValue("test", "DropCol");
            nq.AddValue("isGeneric", false);
            nq.AddValue("numColumns", lCols.Count);
            nq.AddValue("numRows", numRows);
            nq.AddValue("percentNull", percentNull);
            nq.AddValue("computationTime", ct2);
            nq.AddValue("executionTime", et2);
            nq.AddValue("processingTime", pt2);
            resultsChannel.ApplyDML(nq);

            lCols.RemoveAt(colToDrop);
        }

        private void CRUDInstance(List<string> lCols, int numRows, int percentNulls)
        {
            channel1.InitializeDiagnostics();
            channel2.InitializeDiagnostics();
            resultsChannel.InitializeDiagnostics();

            // Generate a list of 10 GUIDs 
            List<Guid> lg = new List<Guid>(10);
            for (int i = 0; i < 10; i++) lg.Add(Guid.NewGuid());

            // 10 inserts
            NonQuery nq = new NonQuery(null);
            for (int i = 0; i < 10; i++)
            {
                nq.Add("TestTable", lg[i], DMLType.INSERT);
                for (int j = 0; j < lCols.Count; j++) if (r.Next(0, 100) >= percentNulls) nq.AddValue(lCols[j], r.Next(64));
            }
            channel1.ApplyDML(nq.Clone());
            channel2.ApplyDML(nq);


            // 10 selects by Guid
            for (int i = 0; i < 10; i++)
            {
                // Create query objects
                Command c1 = new Command();
                Command c2 = new Command();
                string alias1 = c1.nextTableAlias("TestTable");
                string alias2 = c2.nextTableAlias("TestTable");
                TableRef tr1 = new TableRef("TestTable", alias1);
                TableRef tr2 = new TableRef("TestTable", alias2);
                Filter f1 = new Filter(tr1);
                Filter f2 = new Filter(tr2);
                c1.root = f1;
                c2.root = f2;

                // Add id column and id condition
                tr1.AddColumn("id", "TestTable_id", new GuavaData.Domain.UniqueIdentifier());
                tr2.AddColumn("id", "TestTable_id", new GuavaData.Domain.UniqueIdentifier());
                f1.AddCondition("id", Condition.NewCondition(ConditionType.EQ, lg[i]));
                f2.AddCondition("id", Condition.NewCondition(ConditionType.EQ, lg[i]));
                for (int j = 0; j < lCols.Count; j++)
                {
                    tr1.AddColumn(lCols[j], "TestTable_" + lCols[j], new GuavaData.Domain.TinyInteger());
                    tr2.AddColumn(lCols[j], "TestTable_" + lCols[j], new GuavaData.Domain.TinyInteger());
                }

                string s;
                channel1.PoseQueryCommand(c1, out s);
                channel2.PoseQueryCommand(c2, out s);
            }

            // 10 updates
            nq = new NonQuery(null);
            for (int i = 0; i < 10; i++)
            {
                nq.Add("TestTable", lg[i], DMLType.UPDATE);
                for (int j = 0; j < lCols.Count; j++) if (r.Next(0, 100) >= percentNulls) nq.AddValue(lCols[j], r.Next(64));
            }
            channel1.ApplyDML(nq.Clone());
            channel2.ApplyDML(nq);


            // 10 deletes
            nq = new NonQuery(null);
            for (int i = 0; i < 10; i++) nq.Add("TestTable", lg[i], DMLType.DELETE);
            channel1.ApplyDML(nq.Clone());
            channel2.ApplyDML(nq);


            // Build a result row for output
            long ct1 = channel1.CompTime;
            long et1 = channel1.ExTime;
            long pt1 = channel1.PostTime;
            long ct2 = channel2.CompTime;
            long et2 = channel2.ExTime;
            long pt2 = channel2.PostTime;

            nq = new NonQuery(null);
            nq.Add("Results", Guid.NewGuid(), DMLType.INSERT);
            nq.AddValue("test", "CRUD");
            nq.AddValue("isGeneric", true);
            nq.AddValue("numColumns", lCols.Count);
            nq.AddValue("numRows", numRows);
            nq.AddValue("percentNull", percentNulls);
            nq.AddValue("computationTime", ct1);
            nq.AddValue("executionTime", et1);
            nq.AddValue("processingTime", pt1);
            nq.Add("Results", Guid.NewGuid(), DMLType.INSERT);
            nq.AddValue("test", "CRUD");
            nq.AddValue("isGeneric", false);
            nq.AddValue("numColumns", lCols.Count);
            nq.AddValue("numRows", numRows);
            nq.AddValue("percentNull", percentNulls);
            nq.AddValue("computationTime", ct2);
            nq.AddValue("executionTime", et2);
            nq.AddValue("processingTime", pt2);
            resultsChannel.ApplyDML(nq);
        }

        private void QueryInstance(List<string> lCols, int numRows, int percentNulls)
        {
            ConditionType[] lConds = new ConditionType[] { ConditionType.EQ, ConditionType.GE, ConditionType.GT, ConditionType.LE, ConditionType.LT, ConditionType.NEQ };

            int numQueryColumns = 4;
            int numReturnColumns = 4;

            // Initialize diagnostics for channels
            channel1.InitializeDiagnostics();
            channel2.InitializeDiagnostics();
            resultsChannel.InitializeDiagnostics();

            for (int item = 0; item < 10; item++)
            {
                // Create random query objects
                Command c1 = new Command();
                Command c2 = new Command();
                string alias1 = c1.nextTableAlias("TestTable");
                string alias2 = c2.nextTableAlias("TestTable");
                TableRef tr1 = new TableRef("TestTable", alias1);
                TableRef tr2 = new TableRef("TestTable", alias2);
                Filter f1 = new Filter(tr1);
                Filter f2 = new Filter(tr2);
                c1.root = f1;
                c2.root = f2;
                List<int> lQuery = new List<int>();
                List<int> lReturn = new List<int>();

                // Create a list of columns to query
                for (int iCount = 0; iCount < numQueryColumns; iCount++)
                {
                    int current = r.Next(lCols.Count);
                    while (lQuery.Contains(current)) current = r.Next(lCols.Count);
                    lQuery.Add(current);
                }

                // Create a list of columns to query
                for (int iCount = 0; iCount < numReturnColumns; iCount++)
                {
                    int current = r.Next(lCols.Count);
                    while (lReturn.Contains(current)) current = r.Next(lCols.Count);
                    lReturn.Add(current);
                }





                for (int j = 0; j < lCols.Count; j++)
                {
                    tr1.AddColumn(lCols[j], "TestTable_" + lCols[j], new GuavaData.Domain.TinyInteger());
                    tr2.AddColumn(lCols[j], "TestTable_" + lCols[j], new GuavaData.Domain.TinyInteger());
                }

                
                
                
                // Add the query columns to the query
                foreach (int iCol in lQuery)
                {
                    // Create query condition
                    byte condValue = Convert.ToByte(r.Next(64));
                    ConditionType condType = lConds[r.Next(lConds.Length)];
                    Condition cond = Condition.NewCondition(condType, condValue);

                    if (lReturn.Contains(iCol))
                    {
                        tr1.AddColumn(lCols[iCol], "TestTable_" + lCols[iCol], new GuavaData.Domain.TinyInteger());
                        tr2.AddColumn(lCols[iCol], "TestTable_" + lCols[iCol], new GuavaData.Domain.TinyInteger());
                    }
                    f1.AddCondition(lCols[iCol], cond);
                    f2.AddCondition(lCols[iCol], cond);
                }

                // Add the remaining columns as print columns
                foreach (int iCol in lReturn)
                {
                    if (!(lQuery.Contains(iCol)))
                        tr1.AddColumn(lCols[iCol], "TestTable_" + lCols[iCol], new GuavaData.Domain.TinyInteger());
                    if (!(lQuery.Contains(iCol)))
                        tr2.AddColumn(lCols[iCol], "TestTable_" + lCols[iCol], new GuavaData.Domain.TinyInteger());
                }

                // Query is built, so execute
                string s;
                channel1.PoseQueryCommand(c1, out s);
                channel2.PoseQueryCommand(c2, out s);
            }

            // Build a result row for output
            long ct1 = channel1.CompTime;
            long et1 = channel1.ExTime;
            long pt1 = channel1.PostTime;
            long ct2 = channel2.CompTime;
            long et2 = channel2.ExTime;
            long pt2 = channel2.PostTime;

            NonQuery nq = new NonQuery(null);
            nq.Add("Results", Guid.NewGuid(), DMLType.INSERT);
            nq.AddValue("test", "query");
            nq.AddValue("isGeneric", true);
            nq.AddValue("numColumns", lCols.Count);
            nq.AddValue("numRows", numRows);
            nq.AddValue("percentNull", percentNulls);
            nq.AddValue("computationTime", ct1);
            nq.AddValue("executionTime", et1);
            nq.AddValue("processingTime", pt1);
            nq.Add("Results", Guid.NewGuid(), DMLType.INSERT);
            nq.AddValue("test", "query");
            nq.AddValue("isGeneric", false);
            nq.AddValue("numColumns", lCols.Count);
            nq.AddValue("numRows", numRows);
            nq.AddValue("percentNull", percentNulls);
            nq.AddValue("computationTime", ct2);
            nq.AddValue("executionTime", et2);
            nq.AddValue("processingTime", pt2);
            resultsChannel.ApplyDML(nq);
        }

        private void btnOuterJoinTest_Click(object sender, EventArgs e)
        {
            TestOuterJoin();
        }
    }
}